/**
 * ----------------------------------------------------------------------------
 * Copyright Jens Konopka
 * 
 * Alle Rechte vorbehalten.
 * Licence: Apache 2.0
 * ----------------------------------------------------------------------------
 *
 * created on 02.03.2012 by jkonopka
 */
package de.sql.toolkit.provider;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import de.sql.toolkit.Resource;
import de.sql.toolkit.manager.ConnectionManager;
import de.sql.toolkit.model.Table;
import de.sql.toolkit.util.SqlUtil;

/**
 * @author jkonopka
 * 
 */
public class TableProvider implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 6240932713136488039L;

	public List<Table> select() throws Exception {
		return select(null);
	}

	public List<Table> select(final String schema) throws Exception {
		final List<Table> list = new ArrayList<Table>();
		final Connection connection = ConnectionManager.getConnection();
		ResultSet result = null;
		Statement statement = null;

		try {

			final StringBuilder sql = new StringBuilder();
			sql.append(Resource.getSqlSpecProp("sql.statement.schema.tables"));
			sql.append(" ");

			if (schema != null) {
				sql.append("WHERE ");
				sql.append(Resource.getSqlSpecProp("sql.table.schema"));
				sql.append(" = '");
				sql.append(schema);
				sql.append("' AND ");
				sql.append(Resource.getSqlSpecProp("sql.table.type"));
				sql.append(" = '");
				sql.append("BASE TABLE");
				sql.append("'");
			}

			// SELECT table_name, table_type, engine
			// -> FROM information_schema.tables
			// -> WHERE table_schema = 'db5'

			statement = connection.createStatement();
			result = statement.executeQuery(sql.toString());
			while (result.next()) {
				final Table entity = new Table();
				entity.setCatalog(result.getString(Resource.getSqlSpecProp("sql.table.catalog")));
				entity.setSchema(result.getString(Resource.getSqlSpecProp("sql.table.schema")));
				entity.setName(result.getString(Resource.getSqlSpecProp("sql.table.name")));
				entity.setType(result.getString(Resource.getSqlSpecProp("sql.table.type")));
				list.add(entity);
			}

		} finally {
			SqlUtil.close(statement);
			SqlUtil.close(result);
		}
		return list;
	}

}
